{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "fb724b01-db15-468e-9920-61dad188f0f9",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# 电影评分数据分析案例\n",
    "\n",
    "电影评分数据集：\n",
    "```\n",
    "u.data -- \n",
    "The full u data set, 100000 ratings by 943 users on 1682 items.\n",
    "Each user has rated at least 20 movies.\n",
    "Users and items are numbered consecutively from 1.\n",
    "The data is randomly ordered.\n",
    "This is a tab separated list of:\n",
    "user id | item id | rating | timestamp.\n",
    "```\n",
    "我们可以了解到：\n",
    "* 数据集包含 943个用户对1682部电影的100000条评分数据\n",
    "* 每个用户至少对20部电影进行了评分\n",
    "* 电影ID和用户ID都是从1开始的连续编号\n",
    "* 数据是随机排序的\n",
    "* 数据用tab进行分割，并且包含：用户ID、电影ID、评分、时间\n",
    "\n",
    "需求：\n",
    "* 查询用户平均分\n",
    "* 查询电影平均分\n",
    "* 查询大于平均分的电影的数量\n",
    "* 查询高分电影（>3）中打分次数最多的用户，并求出此人打的平均分\n",
    "* 查询每个用户的平均打分、最低打分、最高打分\n",
    "* 查询被评分超过100次的电影的平均分排名前10的电影"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "aeb631f7-8f42-4aa8-b92f-f0ff4f929013",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## 加载数据并简单看一下"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "45e67d1f-e329-4800-b1d8-4d2a854342dd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.types import StructType, StringType, IntegerType\n",
    "\n",
    "schema = StructType().add(\"user_id\", StringType(), nullable=True).\\\n",
    "add(\"movie_id\", StringType(), nullable=True).\\\n",
    "add(\"rank\", IntegerType(), nullable=True).\\\n",
    "add(\"ts\", StringType(), nullable=True)\n",
    "\n",
    "df = spark.read.csv(\"/mnt/databrickscontainer1/u.data\", schema=schema, sep=\"\\t\")\n",
    "\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "8beb73b1-ed2a-43cd-a7c4-2815f49f88d8",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## 使用DSL语法风格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "de11375c-2f56-4441-9fc9-4f5fbe194b96",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "import pyspark.sql.functions as F"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "d9df8867-1011-43c1-ae09-d98ad613ceca",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询用户平均分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "91681ffe-0417-480f-861c-36fb3318b25c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "display(df.groupBy(\"user_id\").agg(F.mean(\"rank\"), F.round(F.mean(\"rank\"), 2).alias(\"avg_rank\")).orderBy(\"avg_rank\",ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "7e7f6a86-2937-47bd-a396-6c7a952bf930",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询电影平均分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "4dbeb502-f0d1-4713-8fd2-b3da9b7bf87d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "display(df.groupBy(\"movie_id\").agg(F.mean(\"rank\"), F.round(F.mean(\"rank\"), 2).alias(\"avg_rank\")).orderBy(\"avg_rank\",ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "5222aadd-0024-4247-b41e-ee97bb74fa24",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询大于平均分的电影的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "fd5b0084-328c-4165-ae49-694b55a6e8c1",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "print(df.where(df[\"rank\"] > df.select(F.mean(\"rank\").alias(\"rank\")).first()[\"rank\"]).groupBy(\"movie_id\").count().count())\n",
    "\n",
    "display(df.where(df[\"rank\"] > df.select(F.mean(\"rank\").alias(\"rank\")).first()[\"rank\"]).groupBy(\"movie_id\").count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "351f1ce5-4e33-4e62-bf1c-ece987177050",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询高分电影（>3）中打分次数最多的用户，并求出此人打的平均分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "a1b66a69-a2ef-4c97-8a68-02651422b445",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "print(df.where(\"rank > 3\").groupBy(\"user_id\").count().orderBy(\"count\", ascending=False).first()[\"user_id\"])\n",
    "\n",
    "display(df.where(df[\"user_id\"] == df.where(\"rank > 3\").groupBy(\"user_id\").count().orderBy(\"count\", ascending=False).first()[\"user_id\"]).agg(F.mean(\"rank\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "bba54bf3-753c-4cb3-9dfe-79dce5e8cc4b",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询每个用户的平均打分、最低打分、最高打分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "937abd6e-d148-49b1-bc6c-dadedef69576",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "display(df.groupBy(\"user_id\").agg(F.mean(\"rank\"), F.min(\"rank\"), F.max(\"rank\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "4ea9168f-ffd6-42df-bad1-1ff968f99371",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询被评分超过100次的电影的平均分排名前10的电影"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "67c57413-3c6d-4ad1-9beb-c02460acea40",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.groupBy(\"movie_id\").agg(F.count(\"movie_id\").alias(\"count\"),F.mean(\"rank\").alias(\"mean_rank\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "29dc89ac-19ed-4284-87b9-6e267cbb3264",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.groupBy(\"movie_id\").agg(F.count(\"movie_id\").alias(\"count\"),F.mean(\"rank\").alias(\"mean_rank\")).where(\"count > 100\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "f779448c-4394-482f-85a9-8052d2aaa89d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "display(df.groupBy(\"movie_id\").agg(F.count(\"movie_id\").alias(\"count\"),F.mean(\"rank\").alias(\"mean_rank\")).where(\"count > 100\").orderBy(\"mean_rank\", ascending=False).limit(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "a66d9ffe-2610-4fff-a25a-4ecb8cd2a00b",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## 使用SQL语法风格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "c6b788d0-d1cd-46d9-887a-1a9e501094fb",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.createOrReplaceTempView(\"movie_rank\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "16b317c0-c967-46df-bd08-69465484d6fe",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询用户平均分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "581289de-cbf8-449c-9b1f-fd27c3bf9198",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select user_id, mean(rank), round(mean(rank),2) from movie_rank group by user_id order by mean(rank) desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "371e1d3d-1456-40e8-9948-6d5f5b762b62",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询电影平均分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "64485ba3-1018-4255-87bf-44eaee699799",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select movie_id, mean(rank), round(mean(rank),2) from movie_rank group by movie_id order by mean(rank) desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "c09fec80-31d4-4c29-a86f-211142a214be",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询大于平均分的电影的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "8656847f-08a1-46e1-943d-67a94e61c4ec",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "\n",
    "select avg(rank) from movie_rank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "ea545b73-205a-4207-88a1-74c157466595",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select movie_id,count(*) from movie_rank where rank > (select avg(rank) from movie_rank) group by movie_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "75bf5089-3f1a-4a15-8974-6c52180ff389",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select count(*) from (\n",
    "select movie_id,count(*) from movie_rank where rank > (select avg(rank) from movie_rank) group by movie_id\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "f8c32f6d-cac6-4460-8926-78a7e6655da1",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询高分电影（>3）中打分次数最多的用户，并求出此人打的平均分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "551f4505-0a2c-419d-9ad1-c4884fa8d181",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "\n",
    "select user_id from movie_rank where rank > 3 group by user_id order by count(1) desc limit 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "fd23f60a-8af3-4989-b546-93e238528583",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select user_id, avg(rank), round(avg(rank), 2) from movie_rank where user_id = (select user_id from movie_rank where rank > 3 group by user_id order by count(1) desc limit 1) group by user_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "30ee88ac-7dec-4f97-ae30-bb6db9e45ff0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询每个用户的平均打分、最低打分、最高打分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "7752b271-5eb7-4256-9847-6e4b19c677a8",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select user_id, avg(rank), min(rank), max(rank) from movie_rank group by user_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "79cc186e-4905-4370-8af9-c4b04b541b63",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "* 查询被评分超过100次的电影的平均分排名前10的电影"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "74f58cb1-908c-476d-ac7f-f2feb2e765bd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select movie_id, count(*), mean(rank) from movie_rank group by movie_id having count(1) > 100 order by 3 desc limit 10"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {},
   "notebookName": "电影评分数据分析案例",
   "notebookOrigID": 2180139033493891,
   "widgets": {}
  },
  "kernelspec": {
   "display_name": "",
   "name": ""
  },
  "language_info": {
   "name": ""
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
